# 定义 数据库工具类
import pymysql


class DBTools:
    # 创建连接
    @classmethod
    def __create_conn(cls):
        # conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="123456", database="demo",
        #                        charset="utf8mb4", autocommit=False)
        conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="123456", database="ihrm_db",
                               charset="utf8mb4", autocommit=False)
        return conn

    # 查询一条记录
    @classmethod
    def query_one(cls, sql):
        my_conn = None
        my_cursor = None
        res = None
        try:

            # 借助类名 调用 类方法 create_conn 类方法调用：类名.类方法（）
            # 创建连接
            my_conn = DBTools.__create_conn()
            # 创建游标
            my_cursor = my_conn.cursor()
            # 执行 sql 语句 ，做查询
            my_cursor.execute(sql)
            res = my_cursor.fetchone()
        except Exception as err:
            print(f"执行查询SQL信息:{err}")
        finally:
            # 关闭游标
            my_cursor.close()
            # 关闭连接
            my_conn.close()
            #返回查询结果
            return res



    # 增删改记录
    @classmethod
    def db_iud(cls, sql):
        my_conn = None
        my_cursor = None
        try:
            # 创建连接
            my_conn = DBTools.__create_conn()
            # 创建游标
            my_cursor = my_conn.cursor()
            # 执行 sql 语句
            my_cursor.execute(sql)
            print(f"Affected rows:{my_conn.affected_rows()}")
            # 增删改 成功 提交事务
            my_conn.commit()
        except Exception as err:
            # 增删改 失败 回滚事务
            my_conn.rollback()
            #打印错误信息
            print(f"增删改SQL语句错误信息:{err}")
        finally:
            # 关闭游标
            my_cursor.close()
            # 关闭连接
            my_conn.close()
            return f"Affected rows:{my_conn.affected_rows()}"





if __name__ == '__main__':
    # db = DBTools()
    # db.create_conn()
    # result = DBTools.query_one("select * from bs_user;")
    # print(f"查询语句结果:{result}")
    # DBTools.db_iud("insert INTO bs_user(id,mobile,username,is_delete) values('77889900','13812312302','麻辣烫',0);")
    # DBTools.db_iud("delete from bs_user where id ='77889900';")
    db = DBTools.db_iud("insert INTO ihrm_emp(序号,姓名,手机号,工号) values('4','王小','88888888','987456');")
    print(db)